import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

public class Entity {
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://www.guoruiming.com:3306/reservations?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "123456";

    private Connection conn = null;

    public Entity() {
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Interval[] queryIntervalsByDate(java.util.Date date) {
        ArrayList<Interval> intervals;
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String startDate = format.format(date) + " 00:00:00";
        String endDate = format.format(date) + " 23:59:59";

        String query = "SELECT * FROM dates WHERE start BETWEEN '" + startDate + "' and '" + endDate + "';";
        System.out.println(query);
        executeQuery(query);
        return null;
    }

    public void executeQuery() {
        try {
            // 执行查询
            System.out.println("实例化Statement对象...");
            Statement stmt = conn.createStatement();
            String query;
            query = "SELECT start, id, duration, booked FROM dates";
            ResultSet rs = stmt.executeQuery(query);

            // 展开结果集数据库
            while (rs.next()) {
                // 通过字段检索
                java.sql.Date date = rs.getDate("start");
                java.sql.Time time = rs.getTime("start");
                int id = rs.getInt("id");
                int duration = rs.getInt("duration");
                boolean booked = rs.getBoolean("booked");

                date.setTime(date.getTime() + (long) duration * 1000 * 60 * 60);
                // 输出数据
                System.out.print("date: " + date + "time" + time + " id " + id + " duration " + duration + " " + (booked ? "BOOKED" : "not booked") + "\n");
            }
            // 完成后关闭
            rs.close();
            stmt.close();
        } catch (Exception se) {
            se.printStackTrace();
        }
        System.out.println("Goodbye!");
    }

    public ResultSet executeQuery(String query) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 执行查询
            System.out.println("实例化Statement对象...");
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            // 完成后关闭
            rs.close();
            stmt.close();
        } catch (Exception se) {
            se.printStackTrace();
        }
        System.out.println("Goodbye!");
        return rs;
    }

    public void getAvailableTimes() {
        String query = "SELECT * FROM dates ;";
        executeQuery(query);
    }

    public void getIntervals() {
        String query = "SELECT * FROM dates;";
        ResultSet rs = executeQuery(query);
        while (true) {
            try {
                if (!rs.next()) break;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void close() {
        // 关闭资源
        try {
            if (conn != null && !conn.isClosed()) conn.close();
        } catch (SQLException ignored) {   // 什么都不做
        }
    }

    public boolean tryBook(int idToBook, int userId) {
        // 拿到idToBook对应的元组
        // 判断是否已经被预约
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String query = "SELECT booked, bookedBy FROM dates WHERE id=" + idToBook + ";";
        try {
            assert stmt != null;
            rs = stmt.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            assert rs != null;
            if (rs.next()) {
                if (rs.getBoolean("booked")) {
                    if (rs.getInt("bookedBy") == userId) {
                        System.out.println("你已经预约此时段！");
                    } else {
                        System.out.println("id=" + idToBook + "已被预约，请选择其他时段！");
                    }
                    return false;
                } else {
                    query = "UPDATE dates SET booked=true, bookedBy=" + userId + " WHERE id=" + idToBook + ";";
                    System.out.println(query);
                    stmt.execute(query);
                    System.out.println("成功预约");
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean tryUnbook(int idToUnbook, int userId) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String query = "SELECT booked, bookedBy FROM dates WHERE id=" + idToUnbook + ";";
        try {
            assert stmt != null;
            rs = stmt.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            assert rs != null;
            if (rs.next()) {
                if (rs.getBoolean("booked")) {
                    if (rs.getInt("bookedBy") == userId) {
                        query = "UPDATE dates SET booked=false, bookedBy=NULL WHERE id=" + idToUnbook + ";";
                        System.out.println("取消预约成功！");
                    } else {
                        System.out.println("该时段被他人预约！");
                        return false;
                    }
                } else {
                    System.out.println("该时段并没有被预约！");
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
